package com.sms.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.sms.dao.IReportDAO;
import com.sms.db.DBOperator;
import com.sms.dto.TestNumberDto;
import com.sms.po.TInvalidmsg;
import com.sms.po.TMsgRpt;
import com.sms.po.TSendmsg;
import com.sms.po.TWaitsend;

/**
 * @author 20111012 by meng.xf
 */
public class ReportDAO extends HibernateDaoSupport implements IReportDAO {
	private static final Log log = LogFactory.getLog(ReportDAO.class);

	@Override
	public List<TSendmsg> showSmsDetailByPage(DetachedCriteria criteria, int start,int limit) {
		log.debug("finding all smsDetail by pages");
		try{
			return super.getHibernateTemplate().findByCriteria(criteria, start, limit);
		}catch(RuntimeException e){
			log.debug("finding all smsDetail failed:",e);
			throw e;
		}
	}
	
	@Override
	public int getSmsDetailCount(DetachedCriteria criteria) {
		return super.getHibernateTemplate().findByCriteria(criteria).size();
	}

	@Override
	public int getTestNumberCount(String userId) {
		String sql="SELECT COUNT(*) FROM(SELECT user_id,phone,COUNT(1) AS totals FROM t_hist_sendmsg"
			+ " WHERE user_id='"+userId+"'"
			+ " AND phone NOT IN(SELECT phone FROM whitelist WHERE user_id='"+userId+"')"
			+ " GROUP BY user_id,phone) AS t";
//		System.out.println(sql);
		int rowCount=0;
		Connection conn = DBOperator.getConnection();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			if(rs.next()){
				rowCount=rs.getInt(1);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			logger.error(e);
		}finally{
			DBOperator.closeConnection(conn);
		}
		return rowCount;
	}

	@Override
	public List<TestNumberDto> showTestNumberByPage(String userId, int start,
			int limit) {
		String sql="SELECT user_id,phone,COUNT(1) AS totals FROM t_hist_sendmsg"
			+ " WHERE user_id='"+userId+"'"
			+ " AND phone NOT IN(SELECT phone FROM whitelist WHERE user_id='"+userId+"')"
			+ " GROUP BY user_id,phone ORDER BY totals DESC"
			+ " LIMIT "+start+","+limit;
		
		List<TestNumberDto> list=new ArrayList<TestNumberDto>();
		Connection conn = DBOperator.getConnection();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				TestNumberDto ca=new TestNumberDto();
				ca.setUserId(rs.getString("user_id"));
				ca.setPhone(rs.getString("phone"));
				ca.setTotals(rs.getInt("totals"));
				list.add(ca);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
			return null;
		}finally{
			DBOperator.closeConnection(conn);
		}
		return list;
	}
	
	@Override
	public int getTestNumber2Count(String userId,String searchStr) {
		String sql="SELECT COUNT(*) FROM t_hist_sendmsg"
			+ " WHERE user_id='"+userId+"' AND content like '%"+searchStr+"%'"
			+ " AND phone NOT IN(SELECT phone FROM whitelist WHERE user_id='"+userId+"')";
//		System.out.println(sql);
		int rowCount=0;
		Connection conn = DBOperator.getConnection();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			if(rs.next()){
				rowCount=rs.getInt(1);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			logger.error(e);
		}finally{
			DBOperator.closeConnection(conn);
		}
		return rowCount;
	}

	@Override
	public List<TestNumberDto> showTestNumber2ByPage(String userId, String searchStr, int start,
			int limit) {
		String sql="SELECT user_id,phone,content,senddate FROM t_hist_sendmsg"
			+ " WHERE user_id='"+userId+"' AND content like '%"+searchStr+"%'"
			+ " AND phone NOT IN(SELECT phone FROM whitelist WHERE user_id='"+userId+"')"
			+ " ORDER BY id DESC"
			+ " LIMIT "+start+","+limit;
		
		List<TestNumberDto> list=new ArrayList<TestNumberDto>();
		Connection conn = DBOperator.getConnection();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				TestNumberDto ca=new TestNumberDto();
				ca.setUserId(rs.getString("user_id"));
				ca.setPhone(rs.getString("phone"));
				ca.setContent(rs.getString("content"));
				ca.setLastSenddate(rs.getString("senddate"));
				list.add(ca);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
			return null;
		}finally{
			DBOperator.closeConnection(conn);
		}
		return list;
	}
	
	//通道发送统计
	@Override
	public List<TMsgRpt> showRouteSendReportByPage(Map<String,String> map, int start, int limit) {
		String beginDate=map.get("beginDate");
		String endDate=map.get("endDate");
		String reportType=map.get("reportType");
		
		String dateintStr="dateint";
		if("1".equals(reportType)){//按天统计
			dateintStr="dateint";
		}else if("2".equals(reportType)){//按月统计
			dateintStr="cast(dateint/100 as decimal) as dateint";
		}
		
		String sql="SELECT "+dateintStr+",route_id,SUM(totals) AS totals,SUM(succ_totals) AS succ_totals,"
				+"SUM(fail_totals) AS fail_totals "
				+" FROM t_msg_rpt WHERE"
				+" dateint>="+beginDate+" AND dateint<="+endDate
				+" GROUP BY dateint,route_id ORDER BY dateint "
				+"LIMIT "+start+","+limit;
//		System.out.println(sql);
		Connection conn = DBOperator.getConnection();
		List<TMsgRpt> list=new ArrayList<TMsgRpt>();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				TMsgRpt ca=new TMsgRpt();
				ca.setDateint(rs.getInt("dateint"));
				ca.setRouteId(rs.getInt("route_id"));
				ca.setTotals(rs.getInt("totals"));
				ca.setSuccTotals(rs.getInt("succ_totals"));
				ca.setFailTotals(rs.getInt("fail_totals"));
				list.add(ca);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
			return null;
		}finally{
			DBOperator.closeConnection(conn);
		}
		return list;
	}
	
	@Override
	public int getRouteSendReportCount(Map<String,String> map) {
		String beginDate=map.get("beginDate");
		String endDate=map.get("endDate");
		String reportType=map.get("reportType");
		
		String dateintStr="dateint";
		if("1".equals(reportType)){//按天统计
			dateintStr="dateint";
		}else if("2".equals(reportType)){//按月统计
			dateintStr="cast(dateint/100 as decimal) as dateint";
		}
		
		String sql="select count(*) from (SELECT "+dateintStr+",route_id,SUM(totals) AS totals,SUM(succ_totals) AS succ_totals,"
				+" SUM(fail_totals) AS fail_totals"
				+" FROM t_msg_rpt WHERE"
				+" dateint>="+beginDate+" AND dateint<="+endDate
				+" GROUP BY dateint,route_id ORDER BY dateint) as t";
//		System.out.println(sql);
		int rowCount=0;
		Connection conn = DBOperator.getConnection();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			if(rs.next()){
				rowCount=rs.getInt(1);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			logger.error(e);
		}finally{
			DBOperator.closeConnection(conn);
		}
		return rowCount;
	}

	//用户发送统计报表
	@Override
	public List<TMsgRpt> showUserSendReportByPage(Map<String,String> map, int start, int limit) {
		String beginDate=map.get("beginDate");
		String endDate=map.get("endDate");
		String reportType=map.get("reportType");
		String userId=map.get("userId");
		
		String dateintStr="dateint";
		if("1".equals(reportType)){//按天统计
			dateintStr="dateint";
		}else if("2".equals(reportType)){//按月统计
			dateintStr="cast(dateint/100 as decimal) as dateint";
		}
		String userIdStr="";
		if(userId!=null && !"".equals(userId.trim()))
			userId=" and user_id='"+userId+"'";
		
		String sql="SELECT "+dateintStr+",user_id,SUM(totals) AS totals,SUM(succ_totals) AS succ_totals,"
				+"SUM(fail_totals) AS fail_totals,SUM(discount_totals) AS discount_totals"
				+" FROM t_msg_rpt WHERE"
				+" dateint>="+beginDate+" AND dateint<="+endDate
				+ userIdStr
				+" GROUP BY dateint,user_id ORDER BY dateint "
				+"LIMIT "+start+","+limit;
//		System.out.println(sql);
		Connection conn = DBOperator.getConnection();
		List<TMsgRpt> list=new ArrayList<TMsgRpt>();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while(rs.next()){
				TMsgRpt ca=new TMsgRpt();
				ca.setDateint(rs.getInt("dateint"));
				ca.setUserId(rs.getString("user_id"));
				ca.setTotals(rs.getInt("totals"));
				ca.setSuccTotals(rs.getInt("succ_totals"));
				ca.setFailTotals(rs.getInt("fail_totals"));
				ca.setDiscountTotals(rs.getInt("discount_totals"));
				list.add(ca);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
			return null;
		}finally{
			DBOperator.closeConnection(conn);
		}
		return list;
	}
	
	@Override
	public int getUserSendReportCount(Map<String,String> map) {
		String beginDate=map.get("beginDate");
		String endDate=map.get("endDate");
		String reportType=map.get("reportType");
		String userId=map.get("userId");
		
		String dateintStr="dateint";
		if("1".equals(reportType)){//按天统计
			dateintStr="dateint";
		}else if("2".equals(reportType)){//按月统计
			dateintStr="cast(dateint/100 as decimal) as dateint";
		}
		String userIdStr="";
		if(userId!=null && !"".equals(userId.trim()))
			userId=" and user_id='"+userId+"'";
		
		String sql="select count(*) from (SELECT "+dateintStr+",user_id,SUM(totals) AS totals,SUM(succ_totals) AS succ_totals,"
				+"SUM(fail_totals) AS fail_totals,SUM(discount_totals) AS discount_totals"
				+" FROM t_msg_rpt WHERE"
				+" dateint>="+beginDate+" AND dateint<="+endDate
				+ userIdStr
				+" GROUP BY dateint,user_id ORDER BY dateint ) as t";
//		System.out.println(sql);
		int rowCount=0;
		Connection conn = DBOperator.getConnection();
		try{
			ResultSet rs = conn.createStatement().executeQuery(sql);
			if(rs.next()){
				rowCount=rs.getInt(1);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			logger.error(e);
		}finally{
			DBOperator.closeConnection(conn);
		}
		return rowCount;
	}

	@Override
	public int getInvalidmsgCount(DetachedCriteria criteria) {
		return super.getHibernateTemplate().findByCriteria(criteria).size();
	}

	@Override
	public List<TInvalidmsg> showInvalidmsgByPage(DetachedCriteria criteria, int start,
			int limit) {
		try{
			return super.getHibernateTemplate().findByCriteria(
					criteria, start, limit);
		}catch(RuntimeException e){
			log.debug("finding all Operlog failed:",e);
			throw e;
		}
	}

	@Override
	public int getWaitsendSmsCount(DetachedCriteria criteria) {
		return super.getHibernateTemplate().findByCriteria(criteria).size();
	}

	@Override
	public List<TWaitsend> showWaitsendSmsByPage(DetachedCriteria criteria,
			int start, int limit) {
		try{
			return super.getHibernateTemplate().findByCriteria(
					criteria, start, limit);
		}catch(RuntimeException e){
			log.debug("finding all waitsend failed:",e);
			throw e;
		}
	}

	

	
}
